{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ef27ae28-2c42-4c14-9ef8-ce108651d017",
   "metadata": {},
   "source": [
    "# Pandas教程\n",
    "\n",
    "### 第一部分: Pandas简介\n",
    "\n",
    "#### Pandas的概念和重要性\n",
    "\n",
    "Pandas是一个开源的Python数据分析库，它提供了高性能、易于使用的数据结构和数据分析工具。Pandas特别适合处理和分析输入数据表格，比如CSV文件数据。Pandas的名字来源于“Panel Data”，意味着它能很好地处理多维数据。\n",
    "\n",
    "使用Pandas，数据科学家和分析师可以高效地进行复杂的数据分析和操作，包括数据清洗、转换、分析和可视化。Pandas支持广泛的数据格式，并提供了大量的功能，可以帮助用户轻松处理缺失数据、合并数据集、时间序列分析等。因此，Pandas是数据科学领域非常重要的一个工具。\n",
    "\n",
    "#### Pandas的安装和基本设置\n",
    "\n",
    "要开始使用Pandas，首先需要确保你的Python环境已安装。Pandas可通过多种方式安装，最常见的是使用pip或conda：\n",
    "\n",
    "1. 使用pip安装Pandas：\n",
    "\n",
    "   ```\n",
    "   pip install pandas\n",
    "   ```\n",
    "\n",
    "2. 使用conda安装Pandas（如果你使用的是Anaconda或Miniconda）：\n",
    "\n",
    "   ```\n",
    "   conda install pandas\n",
    "   ```\n",
    "\n",
    "安装完成后，你可以在Python脚本或交互式环境中导入Pandas来开始使用：\n",
    "\n",
    "```\n",
    "import pandas as pd\n",
    "```\n",
    "\n",
    "通常，我们导入Pandas时使用`pd`这个别名，这是一种约定俗成的简写方式。\n",
    "\n",
    "#### Pandas与Python标准库的比较\n",
    "\n",
    "Pandas与Python的标准库，如列表（list）、字典（dict）和基本的数据处理功能有所不同。这里是一些主要区别：\n",
    "\n",
    "- **性能**：Pandas基于NumPy（另一个Python的数值计算库），提供优化的性能特别是在大数据集上。Python的标准库在处理大规模数据时可能不够高效。\n",
    "- **数据结构**：Pandas提供了两种主要的数据结构：`Series`和`DataFrame`，它们支持复杂的数据操作，能进行索引、对齐、重新排列等。而Python标准库的数据结构如列表和字典不支持这些对于数据分析非常关键的功能。\n",
    "- **功能集成**：Pandas内置了大量数据处理功能，如合并、分组、时间序列分析等，这些在Python标准库中需要手动实现，或者根本无法实现。\n",
    "- **用途**：Pandas专为数据分析和数据处理设计，适用于数据科学项目和复杂数据操作；而Python标准库提供的结构更适合一般的编程任务。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2fc27d7-dd22-4b77-82c8-b1871c01c25a",
   "metadata": {},
   "source": [
    "### 第二部分: Pandas的基础组件\n",
    "\n",
    "#### 2.1 Series的创建与操作\n",
    "\n",
    "##### 2.1.1 Series的概念\n",
    "\n",
    "`Series` 是Pandas中的一维数组结构，每个元素都有各自的标签（也称为索引）。在很多方面，它类似于Python中的字典数据结构，但提供了更丰富的功能和更高的性能。\n",
    "\n",
    "##### 2.1.2 创建Series\n",
    "\n",
    "创建一个`Series`非常简单，你可以使用列表、字典或NumPy数组等数据结构。例如："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "25673dcd-cafc-4f0c-93ac-a8868d6a7000",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "dtype: int64\n",
      "a    1\n",
      "b    2\n",
      "c    3\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 通过列表创建Series\n",
    "data = [1, 2, 3, 4]\n",
    "series = pd.Series(data)\n",
    "print(series)\n",
    "\n",
    "# 通过字典创建Series，自动使用字典键作为索引\n",
    "data_dict = {'a': 1, 'b': 2, 'c': 3}\n",
    "series_dict = pd.Series(data_dict)\n",
    "print(series_dict)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f996434a-0a7c-49a2-babe-ae7425f70cdc",
   "metadata": {},
   "source": [
    "##### 2.1.3 基本操作：索引、数据选取、数据赋值\n",
    "\n",
    "`Series` 支持多种数据选取方式，包括通过索引标签和位置索引："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2d60bdf3-9423-4f92-b285-7620adbfc79e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "1\n",
      "0    1\n",
      "1    5\n",
      "2    3\n",
      "3    4\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 通过索引标签选取数据\n",
    "print(series_dict['a'])\n",
    "\n",
    "# 通过位置选取数据\n",
    "print(series[0])\n",
    "\n",
    "# 修改数据\n",
    "series[1] = 5\n",
    "print(series)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9de3fa1a-80c9-400c-82cd-78c81384d26a",
   "metadata": {},
   "source": [
    "#### 2.2 DataFrame的创建与操作\n",
    "\n",
    "##### 2.2.1 DataFrame的概念\n",
    "\n",
    "`DataFrame` 是Pandas中的二维表格型数据结构。它类似于电子表格或SQL表，并且可以看作是多个`Series`对象的集合（共用同一个索引）。\n",
    "\n",
    "##### 2.2.2 创建DataFrame\n",
    "\n",
    "创建`DataFrame`也很简单，常见的方式是使用字典列表或二维数组："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "33b7bde2-466f-4afb-9f6b-01a63778eca9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   a   b     c\n",
      "0  1   2   NaN\n",
      "1  5  10  20.0\n",
      "   Column1  Column2  Column3\n",
      "0        1        2        3\n",
      "1        4        5        6\n"
     ]
    }
   ],
   "source": [
    "# 通过列表的字典创建DataFrame\n",
    "data = [{'a': 1, 'b': 2}, {'a': 5, 'b': 10, 'c': 20}]\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "\n",
    "# 通过二维数组直接创建DataFrame\n",
    "import numpy as np\n",
    "array_data = np.array([[1, 2, 3], [4, 5, 6]])\n",
    "df_array = pd.DataFrame(array_data, columns=['Column1', 'Column2', 'Column3'])\n",
    "print(df_array)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b00f75cb-bbf4-419a-a8cb-604d1e1e777e",
   "metadata": {},
   "source": [
    "##### 2.2.3 数据选择、添加与删除列\n",
    "\n",
    "`DataFrame` 支持通过列名或行标签进行数据选择，以及添加和删除列的操作："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d4b7ecb4-80e0-4577-9623-e2c2f1f6ea93",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   a   b     c\n",
      "0  1   2   NaN\n",
      "1  5  10  20.0\n",
      "0    1\n",
      "1    5\n",
      "Name: a, dtype: int64\n",
      "   a   b     c   d\n",
      "0  1   2   NaN   3\n",
      "1  5  10  20.0  15\n",
      "   a   b     c\n",
      "0  1   2   NaN\n",
      "1  5  10  20.0\n"
     ]
    }
   ],
   "source": [
    "print(df)\n",
    "# 选择列\n",
    "print(df['a'])\n",
    "\n",
    "# 添加列\n",
    "df['d'] = df['a'] + df['b']\n",
    "print(df)\n",
    "\n",
    "# 删除列\n",
    "df.drop('d', axis=1, inplace=True)\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cadd1fd6-00eb-4697-a4dd-cf742461de9c",
   "metadata": {},
   "source": [
    "这里的参数具体意义如下：\n",
    "\n",
    "- `'d'`：这是第一个参数，指定要删除的标签名。在这个例子中，'d' 表示要删除名为 'd' 的列。\n",
    "- `axis=1`：这个参数用来指定操作的轴。在Pandas中，`axis=0` 指的是行操作（默认值），而 `axis=1` 指的是列操作。所以这里 `axis=1` 告诉函数我们想要删除列而非行。\n",
    "- `inplace=True`：这个参数用来控制操作的结果是否直接影响到原始的DataFrame。如果设置为 `True`，那么原始的DataFrame将直接被修改，即原地修改，不返回新的DataFrame对象。如果设置为 `False`（默认值），原始DataFrame不会改变，而是返回一个新的DataFrame，其中已经删除了指定的行或列。\n",
    "\n",
    "总结来说，这行代码的作用是：从DataFrame `df` 中删除名为 'd' 的列，并且直接在原始DataFrame上进行修改，不返回新的DataFrame。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35bc4da4-a825-4ff0-a31c-3c2878f600fe",
   "metadata": {},
   "source": [
    "##### 2.2.4 行操作：选择、添加、删除\n",
    "\n",
    "可以通过索引或条件表达式选择行，也可以添加和删除行："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b7c85b49-956f-424f-894e-48e681a7a1fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   a   b     c\n",
      "0  1   2   NaN\n",
      "1  5  10  20.0\n",
      "a    1.0\n",
      "b    2.0\n",
      "c    NaN\n",
      "Name: 0, dtype: float64\n",
      "   a   b     c\n",
      "0  1   2   NaN\n",
      "1  5  10  20.0\n",
      "2  5  10  15.0\n",
      "   a   b     c\n",
      "0  1   2   NaN\n",
      "1  5  10  20.0\n"
     ]
    }
   ],
   "source": [
    "print(df)\n",
    "# 选择行\n",
    "print(df.iloc[0])  # 通过行号选择\n",
    "\n",
    "# 添加行\n",
    "df.loc[2] = {'a': 5, 'b': 10, 'c': 15}\n",
    "print(df)\n",
    "\n",
    "# 删除行\n",
    "df.drop(2, inplace=True)\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca342dfd-143d-410e-aa79-6c059100b8d6",
   "metadata": {},
   "source": [
    "### 1. 选择行\n",
    "\n",
    "#### 1.1 通过索引标签选择 (`loc`)\n",
    "\n",
    "`loc` 允许你通过索引标签来选择行。如果你的DataFrame的索引是自定义的标签，那么可以使用这些标签来选择数据。\n",
    "\n",
    "```\n",
    "# 假设DataFrame df的索引为自定义的标签\n",
    "result = df.loc['index_label']\n",
    "```\n",
    "\n",
    "#### 1.2 通过整数位置选择 (`iloc`)\n",
    "\n",
    "`iloc` 是基于整数位置的选择方法，这意味着你可以通过行号来索引，这与Python和NumPy的索引方式类似。\n",
    "\n",
    "```\n",
    "# 选择第一行\n",
    "result = df.iloc[0]\n",
    "```\n",
    "\n",
    "#### 1.3 条件选择\n",
    "\n",
    "你也可以使用条件表达式选择行。这通常用于选择满足特定条件的所有行。\n",
    "\n",
    "```\n",
    "# 选择所有'a'列值大于5的行\n",
    "result = df[df['a'] > 5]\n",
    "```\n",
    "\n",
    "### 2. 添加行\n",
    "\n",
    "#### 2.1 使用 `loc`\n",
    "\n",
    "如果你想在DataFrame中添加一行，可以使用 `loc` 加上一个新的索引标签。如果该标签不存在，Pandas会添加一个新行。\n",
    "\n",
    "```\n",
    "# 添加一行，索引为'new_row'，并为各列赋值\n",
    "df.loc['new_row'] = [value1, value2, value3, ...]\n",
    "```\n",
    "\n",
    "### 3. 删除行\n",
    "\n",
    "#### 3.1 使用 `drop()`\n",
    "\n",
    "删除行可以通过指定行标签或行标签的列表，并设置 `axis=0` 来完成。如果`inplace`设置为`True`，则直接在原DataFrame上修改，否则返回一个新的DataFrame。\n",
    "\n",
    "```\n",
    "# 删除索引为'index_label'的行\n",
    "df.drop('index_label', axis=0, inplace=True)\n",
    "\n",
    "# 删除多个指定索引的行\n",
    "df.drop(['index_label1', 'index_label2'], axis=0, inplace=True)\n",
    "```\n",
    "\n",
    "#### 3.2 参数解释\n",
    "\n",
    "- **labels**: 要删除的行的标签或标签列表。\n",
    "- **axis**: 指定操作的轴。对于行操作，该值为`0`。\n",
    "- **inplace**: 是否在原地修改DataFrame。如果设置为`True`，则不返回新的DataFrame，原DataFrame将直接被修改。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "353052bf-cc52-4c2f-8b9d-e537b9c1f1e5",
   "metadata": {},
   "source": [
    "### 第三部分: 数据加载与存储\n",
    "\n",
    "#### 3.1 从不同的数据源加载数据\n",
    "\n",
    "Pandas 提供了多种读取常见数据格式的功能，可以从各种数据源如CSV文件、Excel文件以及SQL数据库中加载数据。\n",
    "\n",
    "##### 3.1.1 读取CSV文件\n",
    "\n",
    "CSV (Comma-Separated Values) 文件是一种常见的数据交换格式，由逗号分隔的文本文件。使用Pandas读取CSV文件非常简单："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c659356d-09d9-49e4-a12f-22247a020c0f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Movie Title  Release Year       Director      Genre  \\\n",
      "0           The Unseen          2021       John Doe      Drama   \n",
      "1         Quiet Echoes          2020     Jane Smith   Thriller   \n",
      "2  Challenge of Depths          2022  Alice Johnson  Adventure   \n",
      "3          Star Voyage          2023  Michael Brown     Sci-Fi   \n",
      "4      Forgotten Lands          2019   Chris Wilson    Mystery   \n",
      "\n",
      "   Box Office ($M)  \n",
      "0           150.50  \n",
      "1           100.20  \n",
      "2           300.75  \n",
      "3           400.90  \n",
      "4            50.55  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取CSV文件\n",
    "df = pd.read_csv('1.csv')\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9fa043b2-bb31-4896-9c1a-46c09e899c2f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  电影名称  上映年份             导演         类型  票房（百万美元）\n",
      "0           The Unseen  2021       John Doe      Drama    150.50\n",
      "1         Quiet Echoes  2020     Jane Smith   Thriller    100.20\n",
      "2  Challenge of Depths  2022  Alice Johnson  Adventure    300.75\n",
      "3          Star Voyage  2023  Michael Brown     Sci-Fi    400.90\n",
      "4      Forgotten Lands  2019   Chris Wilson    Mystery     50.55\n",
      "票房总额（百万美元）: 1002.9\n"
     ]
    }
   ],
   "source": [
    "# 更改列名为中文\n",
    "df.columns = ['电影名称', '上映年份', '导演', '类型', '票房（百万美元）']\n",
    "\n",
    "# 打印更新后的DataFrame\n",
    "print(df)\n",
    "\n",
    "# 计算票房总量\n",
    "total_box_office = df['票房（百万美元）'].sum()\n",
    "print(f\"票房总额（百万美元）: {total_box_office}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18e42144-a59f-4c98-984d-a927358c1fa4",
   "metadata": {},
   "source": [
    "你可以通过各种参数来定制如何读取CSV，例如指定分隔符、处理缺失值等。\n",
    "\n",
    "##### 3.1.2 读取Excel文件\n",
    "\n",
    "Excel文件是办公自动化软件常用的数据存储格式。Pandas能够读取和写入Excel文件："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "25d871cb-1422-4335-8cbf-6168f6bfcae4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Collecting openpyxl\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/6a/94/a59521de836ef0da54aaf50da6c4da8fb4072fb3053fa71f052fd9399e7a/openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)\n",
      "     ---------------------------------------- 0.0/250.0 kB ? eta -:--:--\n",
      "     ------ ------------------------------ 41.0/250.0 kB 991.0 kB/s eta 0:00:01\n",
      "     ------------------ ------------------- 122.9/250.0 kB 1.4 MB/s eta 0:00:01\n",
      "     ---------------------------------- --- 225.3/250.0 kB 1.7 MB/s eta 0:00:01\n",
      "     -------------------------------------- 250.0/250.0 kB 1.5 MB/s eta 0:00:00\n",
      "Collecting et-xmlfile (from openpyxl)\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/96/c2/3dd434b0108730014f1b96fd286040dc3bcb70066346f7e01ec2ac95865f/et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)\n",
      "Installing collected packages: et-xmlfile, openpyxl\n",
      "Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2\n"
     ]
    }
   ],
   "source": [
    "!pip install openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "2d454b59-0d87-4717-a64a-c815e3d1c7e3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     电影名称  上映年份       导演  类型  票房（百万美元）\n",
      "0    无人之境  2021    约翰·多伊  剧情    150.50\n",
      "1    静谧回声  2020    简·史密斯  惊悚    100.20\n",
      "2    深渊挑战  2022  艾丽斯·约翰逊  冒险    300.75\n",
      "3    星际航行  2023   迈克尔·布朗  科幻    400.90\n",
      "4  被遗忘的土地  2019  克里斯·威尔逊  悬疑     50.55\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取Excel文件中名为\"Sheet1\"的工作表\n",
    "# df_excel = pd.read_excel('1.xlsx', sheet_name='Sheet1')\n",
    "# 通过索引读取第一个工作表（索引从0开始）\n",
    "df_excel = pd.read_excel('1.xlsx', sheet_name=0)\n",
    "print(df_excel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44e2ba49-c43a-4518-a091-24a40412b6b1",
   "metadata": {},
   "source": [
    "#### 3.2 数据输出\n",
    "\n",
    "将数据写回文件或数据库是数据处理的一个重要环节，Pandas同样支持多种输出格式。\n",
    "\n",
    "##### 3.2.1 数据写入到CSV\n",
    "\n",
    "将DataFrame写入CSV文件同样简单："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "5ec02502-1042-4d7c-8798-bed2ee5fb9aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CSV和Excel文件已成功保存。\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 假设df是已经存在的DataFrame\n",
    "data = {\n",
    "    \"电影名称\": [\"无人之境\", \"静谧回声\", \"深渊挑战\", \"星际航行\", \"被遗忘的土地\"],\n",
    "    \"上映年份\": [2021, 2020, 2022, 2023, 2019],\n",
    "    \"导演\": [\"约翰·多伊\", \"简·史密斯\", \"艾丽斯·约翰逊\", \"迈克尔·布朗\", \"克里斯·威尔逊\"],\n",
    "    \"类型\": [\"剧情\", \"惊悚\", \"冒险\", \"科幻\", \"悬疑\"],\n",
    "    \"票房（百万美元）\": [150.5, 100.2, 300.75, 400.9, 50.55]\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 写入CSV文件，使用GBK编码\n",
    "csv_file_path = 'Movie_Data_CN.csv'\n",
    "df.to_csv(csv_file_path, index=False, encoding='gbk')\n",
    "\n",
    "# 写入Excel文件，指定工作表名称为\"电影数据\"\n",
    "excel_file_path = 'Movie_Data_CN.xlsx'\n",
    "df.to_excel(excel_file_path, index=False, sheet_name='电影数据')\n",
    "\n",
    "print(\"CSV和Excel文件已成功保存。\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "42ffc633-3887-477c-92d0-b8e24aeaf784",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据集:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50\n",
      "4  静谧回声     NaN     None  惊悚       NaN\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建一个包含缺失值和重复行的DataFrame\n",
    "data = {\n",
    "    \"电影名称\": [\"星际航行\", \"星际航行\", \"深渊挑战\", \"无人之境\", \"静谧回声\", \"静谧回声\"],\n",
    "    \"上映年份\": [2023, 2023, 2022, 2021, np.nan, 2020],\n",
    "    \"导演\": [\"迈克尔·布朗\", \"迈克尔·布朗\", \"艾丽斯·约翰逊\", \"约翰·多伊\", None, \"简·史密斯\"],\n",
    "    \"类型\": [\"科幻\", \"科幻\", \"冒险\", \"剧情\", \"惊悚\", \"惊悚\"],\n",
    "    \"票房（百万美元）\": [400.9, 400.9, 300.75, 150.5, None, 100.2]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "print(\"原始数据集:\")\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e98295fe-bb27-4a18-8045-ea4f6f2bde4b",
   "metadata": {},
   "source": [
    "### 第四部分: 数据清洗与预处理\n",
    "\n",
    "#### 4.1 缺失数据的处理\n",
    "\n",
    "##### 4.1.1 发现缺失数据\n",
    "\n",
    "使用 `isnull()` 方法来查找哪些单元格包含缺失值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "21be68e4-b989-41ae-b6d2-9f0e92be79ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   电影名称    上映年份       导演  类型  票房（百万美元）\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50\n",
      "4  静谧回声     NaN     None  惊悚       NaN\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20\n",
      "检查数据中的缺失值:\n",
      "    电影名称   上映年份     导演     类型  票房（百万美元）\n",
      "0  False  False  False  False     False\n",
      "1  False  False  False  False     False\n",
      "2  False  False  False  False     False\n",
      "3  False  False  False  False     False\n",
      "4  False   True   True  False      True\n",
      "5  False  False  False  False     False\n"
     ]
    }
   ],
   "source": [
    "print(df)\n",
    "print(\"检查数据中的缺失值:\")\n",
    "print(df.isnull())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89946cab-cb5e-4290-b633-848f50806a47",
   "metadata": {},
   "source": [
    "##### 4.1.2 填充缺失数据\n",
    "\n",
    "可以使用 `fillna()` 方法来填充缺失数据，例如使用列的均值或者一个固定值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "63172d7a-9fae-4e23-a62a-930520198675",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "填充后的数据:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50\n",
      "4  静谧回声     NaN     None  惊悚    270.65\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20\n"
     ]
    }
   ],
   "source": [
    "# 使用常数值填充缺失数据\n",
    "df_filled_constant = df.fillna(value=0)\n",
    "\n",
    "# 使用列的均值填充缺失数据（仅对数值列）\n",
    "mean_box_office = df['票房（百万美元）'].mean()\n",
    "df['票房（百万美元）'] = df['票房（百万美元）'].fillna(mean_box_office)\n",
    "\n",
    "print(\"填充后的数据:\")\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa19d1cf-f9ad-490b-850d-49e66664eee8",
   "metadata": {},
   "source": [
    "##### 4.1.3 删除缺失数据\n",
    "\n",
    "如果不希望填充缺失值，可以选择删除含有缺失值的行："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ec38f2f0-95b2-441d-9eae-d38ed9674431",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "删除缺失值后的数据:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20\n"
     ]
    }
   ],
   "source": [
    "df_dropped = df.dropna()\n",
    "\n",
    "print(\"删除缺失值后的数据:\")\n",
    "print(df_dropped)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3141c39b-dad5-48fc-b868-810b0bd23bc0",
   "metadata": {},
   "source": [
    "#### 4.2 数据类型转换\n",
    "\n",
    "##### 4.2.1 类型检查和转换\n",
    "\n",
    "可以使用 `dtype` 或 `dtypes` 查看数据类型，使用 `astype()` 方法进行类型转换："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "63ee1615-5b4b-4a8c-bbff-43f102f0ad1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据类型检查:\n",
      "电影名称         object\n",
      "上映年份        float64\n",
      "导演           object\n",
      "类型           object\n",
      "票房（百万美元）    float64\n",
      "dtype: object\n",
      "类型转换后的数据:\n",
      "电影名称         object\n",
      "上映年份        float64\n",
      "导演           object\n",
      "类型           object\n",
      "票房（百万美元）    float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(\"数据类型检查:\")\n",
    "print(df.dtypes)\n",
    "\n",
    "# 将'票房（百万美元）'转换为float类型（如果不是）\n",
    "df['票房（百万美元）'] = df['票房（百万美元）'].astype(float)\n",
    "\n",
    "print(\"类型转换后的数据:\")\n",
    "print(df.dtypes)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0553548d-d4f7-4630-b904-f6243cdd8656",
   "metadata": {},
   "source": [
    "#### 4.3 重复数据的处理\n",
    "\n",
    "可以使用 `duplicated()` 查看重复的行，使用 `drop_duplicates()` 删除重复行："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b87249bb-63ee-45dd-998d-71d84b9c9b59",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "检查重复数据:\n",
      "0    False\n",
      "1     True\n",
      "2    False\n",
      "3    False\n",
      "4    False\n",
      "5    False\n",
      "dtype: bool\n",
      "删除重复数据后的数据集:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50\n",
      "4  静谧回声     NaN     None  惊悚    270.65\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20\n"
     ]
    }
   ],
   "source": [
    "print(\"检查重复数据:\")\n",
    "print(df.duplicated())\n",
    "\n",
    "# 删除重复数据\n",
    "df_no_duplicates = df.drop_duplicates()\n",
    "\n",
    "print(\"删除重复数据后的数据集:\")\n",
    "print(df_no_duplicates)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abcbf30d-d1a7-45d2-a2fd-6ecbb362ae06",
   "metadata": {},
   "source": [
    "#### 4.4 数据过滤与选择\n",
    "\n",
    "可以根据条件过滤数据："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c653d2df-0cf4-43dd-ad79-900132db0c56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "票房大于300百万美元的电影:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75\n"
     ]
    }
   ],
   "source": [
    "# 过滤出票房大于300百万美元的电影\n",
    "high_grossing_movies = df[df['票房（百万美元）'] > 300]\n",
    "\n",
    "print(\"票房大于300百万美元的电影:\")\n",
    "print(high_grossing_movies)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "838283d6-4f13-4f90-a9f7-7667561ae3b7",
   "metadata": {},
   "source": [
    "### 第五部分: 数据分析与操作\n",
    "\n",
    "#### 5.1 排序和排名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f3170f9c-109b-4a37-be9c-5203e3f60233",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "按票房排序的数据:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）  票房排名\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90   5.5\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90   5.5\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75   4.0\n",
      "4  静谧回声     NaN     None  惊悚    270.65   3.0\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50   2.0\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20   1.0\n",
      "添加票房排名后的数据:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）  票房排名\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90   1.5\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90   1.5\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75   3.0\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50   5.0\n",
      "4  静谧回声     NaN     None  惊悚    270.65   4.0\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20   6.0\n"
     ]
    }
   ],
   "source": [
    "# 按票房降序排序\n",
    "df_sorted = df.sort_values(by='票房（百万美元）', ascending=False)\n",
    "\n",
    "print(\"按票房排序的数据:\")\n",
    "print(df_sorted)\n",
    "\n",
    "# 获取票房的排名\n",
    "df['票房排名'] = df['票房（百万美元）'].rank(ascending=False)\n",
    "\n",
    "print(\"添加票房排名后的数据:\")\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf7482df-5a7e-40bc-9b5e-e56aa3434940",
   "metadata": {},
   "source": [
    "5.2 数据聚合与分组操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "67bd306f-bdae-443c-befe-70bbdca46109",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "各类型平均票房:\n",
      "类型\n",
      "冒险    300.750\n",
      "剧情    150.500\n",
      "惊悚    185.425\n",
      "科幻    400.900\n",
      "Name: 票房（百万美元）, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# 根据'类型'进行分组，并计算每个类型的平均票房\n",
    "grouped = df.groupby('类型')\n",
    "average_box_office = grouped['票房（百万美元）'].mean()\n",
    "\n",
    "print(\"各类型平均票房:\")\n",
    "print(average_box_office)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "ea3cccbf-65ef-4bd2-8d99-ea4e151007fd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据集:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50\n",
      "4  静谧回声     NaN     None  惊悚       NaN\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建一个包含缺失值和重复行的DataFrame\n",
    "data = {\n",
    "    \"电影名称\": [\"星际航行\", \"星际航行\", \"深渊挑战\", \"无人之境\", \"静谧回声\", \"静谧回声\"],\n",
    "    \"上映年份\": [2023, 2023, 2022, 2021, np.nan, 2020],\n",
    "    \"导演\": [\"迈克尔·布朗\", \"迈克尔·布朗\", \"艾丽斯·约翰逊\", \"约翰·多伊\", None, \"简·史密斯\"],\n",
    "    \"类型\": [\"科幻\", \"科幻\", \"冒险\", \"剧情\", \"惊悚\", \"惊悚\"],\n",
    "    \"票房（百万美元）\": [400.9, 400.9, 300.75, 150.5, None, 100.2]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "print(\"原始数据集:\")\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a44b829-31ce-4411-a918-ee540e0eebd8",
   "metadata": {},
   "source": [
    "5.3 数据合并与连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "1a478c9f-895a-415a-a1ab-7014900dd03f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "合并后的数据:\n",
      "   电影名称    上映年份       导演  类型  票房（百万美元）  观众评分\n",
      "0  星际航行  2023.0   迈克尔·布朗  科幻    400.90   8.5\n",
      "1  星际航行  2023.0   迈克尔·布朗  科幻    400.90   8.5\n",
      "2  深渊挑战  2022.0  艾丽斯·约翰逊  冒险    300.75   7.8\n",
      "3  无人之境  2021.0    约翰·多伊  剧情    150.50   NaN\n",
      "4  静谧回声     NaN     None  惊悚       NaN   NaN\n",
      "5  静谧回声  2020.0    简·史密斯  惊悚    100.20   NaN\n"
     ]
    }
   ],
   "source": [
    "# 示例数据集\n",
    "other_data = {\n",
    "    \"电影名称\": [\"星际航行\", \"深渊挑战\"],\n",
    "    \"观众评分\": [8.5, 7.8]\n",
    "}\n",
    "df_other = pd.DataFrame(other_data)\n",
    "\n",
    "# 使用merge合并数据\n",
    "df_merged = pd.merge(df, df_other, on='电影名称', how='left')\n",
    "\n",
    "print(\"合并后的数据:\")\n",
    "print(df_merged)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8731f468-a4db-4d2d-b4db-72389148d406",
   "metadata": {},
   "source": [
    "### 第六部分: 高级数据处理\n",
    "\n",
    "#### 6.1 数据透视表的创建和应用\n",
    "\n",
    "数据透视表是一种常用的数据汇总工具，它可以快速对数据集进行分组和汇总，非常适用于探索性数据分析。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "b749e050-3a9d-4076-a2b9-6bd4acf35c35",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据透视表:\n",
      "上映年份   2020   2021    2022   2023\n",
      "类型                               \n",
      "冒险      NaN    NaN  300.75    NaN\n",
      "剧情      NaN  150.5     NaN    NaN\n",
      "惊悚    210.7    NaN     NaN    NaN\n",
      "科幻      NaN    NaN     NaN  400.9\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 假设df是之前的电影数据集\n",
    "data = {\n",
    "    \"电影名称\": [\"星际航行\", \"深渊挑战\", \"无人之境\", \"静谧回声\", \"静谧回声\"],\n",
    "    \"上映年份\": [2023, 2022, 2021, 2020, 2020],\n",
    "    \"导演\": [\"迈克尔·布朗\", \"艾丽斯·约翰逊\", \"约翰·多伊\", \"简·史密斯\", \"简·史密斯\"],\n",
    "    \"类型\": [\"科幻\", \"冒险\", \"剧情\", \"惊悚\", \"惊悚\"],\n",
    "    \"票房（百万美元）\": [400.9, 300.75, 150.5, 100.2, 110.5]\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 创建数据透视表，使用字符串\"sum\"作为聚合函数\n",
    "pivot_table = pd.pivot_table(df, values='票房（百万美元）', index='类型', columns='上映年份', aggfunc='sum')\n",
    "\n",
    "print(\"数据透视表:\")\n",
    "print(pivot_table)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c03fe78c-7475-471e-87f2-9830c7965c7a",
   "metadata": {},
   "source": [
    "#### 6.2 时间序列数据的处理\n",
    "\n",
    "##### 6.2.1 日期和时间的索引\n",
    "\n",
    "时间序列数据通常按日期或时间戳进行索引，这使得时间序列分析变得方便。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "88302a19-d5a0-4ad5-b135-cc228a437cfb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "时间序列数据:\n",
      "2024-01-01    1\n",
      "2024-01-02    2\n",
      "2024-01-03    3\n",
      "Freq: D, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 创建时间序列数据\n",
    "ts_data = pd.Series([1, 2, 3], index=pd.date_range('20240101', periods=3))\n",
    "\n",
    "print(\"时间序列数据:\")\n",
    "print(ts_data)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee77e496-f1cc-4e0d-9a58-3a715e5aa44b",
   "metadata": {},
   "source": [
    "##### 6.2.2 时间序列数据重采样\n",
    "\n",
    "重采样是一种常用的时间序列数据处理技术，用于将时间序列数据从一个频率转换到另一个频率。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0b257dcd-b2d5-486a-a2d5-96538602a359",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "重采样结果:\n",
      "2024-01-31    6\n",
      "Freq: ME, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建时间序列数据\n",
    "ts_data = pd.Series([1, 2, 3], index=pd.date_range('20240101', periods=3))\n",
    "\n",
    "# 按月末重采样并求和，使用'ME'代替原来的'M'\n",
    "resampled_data = ts_data.resample('ME').sum()\n",
    "\n",
    "print(\"重采样结果:\")\n",
    "print(resampled_data)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afb22044-74be-481b-ac69-1fbcd17c8eb5",
   "metadata": {},
   "source": [
    "#### 6.3 分类数据的使用\n",
    "\n",
    "##### 6.3.1 创建和使用Categorical数据类型\n",
    "\n",
    "分类数据类型是用于表示具有有限数量类别的数据特征，这对于提升性能和内存使用效率很有帮助。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "c682991d-907a-4575-bc82-0224fd428267",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据类型检查:\n",
      "category\n"
     ]
    }
   ],
   "source": [
    "# 将电影类型转换为分类数据类型\n",
    "df['类型'] = df['类型'].astype('category')\n",
    "\n",
    "print(\"数据类型检查:\")\n",
    "print(df['类型'].dtypes)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c5c97f5-2913-4f20-b24f-aecc9b4930b2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
